package com.jp.fm.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.data.Category;

public class CategoryDao {

	public static final String CACHE_NAME = "Category";
	public static final String KEYWORD_CACHE_NAME = "KeywordCategory";

	public Set<Category> findAll() {

		Set<Category> cats = new TreeSet<Category>();
		Category cat = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Map<Object, Element> elementMap = cache.getAll(cache
				.getKeysWithExpiryCheck());
		if (null == elementMap || elementMap.size() == 0) {

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("select * from categories order by name");

				rs = p.executeQuery();

				while (rs.next()) {

					String dbName = rs.getString(2);

					cat = new Category(rs.getInt(1), dbName);
					cats.add(cat);
					cache.put(new Element(cat.getId(), cat));
					cache.put(new Element(cat.getName(), cat));
				}

			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			for (Iterator<Element> iterator = elementMap.values().iterator(); iterator
					.hasNext();) {

				Element element = iterator.next();

				cat = (Category) element.getObjectValue();
				cats.add(cat);
			}

		}
		return cats;
	}

	public Map<String, String> findAllKeywordToCategoryMappings() {

		Map<String, String> map = new HashMap<String, String>();
		String keyword = null;
		String categoryName = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(KEYWORD_CACHE_NAME);
		Map<Object, Element> elementMap = cache.getAll(cache
				.getKeysWithExpiryCheck());
		if (null == elementMap || elementMap.size() == 0) {

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();
				p = c.prepareStatement("select keyword, category_name from KEYWORD_CATEGORY_MAP");
				rs = p.executeQuery();

				while (rs.next()) {

					keyword = rs.getString(1);
					categoryName = rs.getString(2);

					map.put(keyword, categoryName);

					cache.put(new Element(keyword, categoryName));

				}

			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			for (Iterator<Element> iterator = elementMap.values().iterator(); iterator
					.hasNext();) {

				Element element = iterator.next();

				categoryName = (String) element.getObjectValue();
				keyword = (String) element.getObjectKey();
				map.put(keyword, categoryName);
			}

		}
		return map;
	}

	public Category findByName(final String name) {

		Category cat = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Element element = cache.get(name);

		if (null == element) {
			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("select * from categories where name = ?");
				p.setString(1, name);

				rs = p.executeQuery();

				while (rs.next()) {

					String dbName = rs.getString(2);

					if (dbName.equals(name)) {
						cat = new Category(rs.getInt(1), dbName);
						cache.put(new Element(cat.getId(), cat));
						cache.put(new Element(cat.getName(), cat));
						break;
					}
				}

				if (null == cat) {
					cat = findByName("Unknown");
				}

			} catch (SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			cat = (Category) element.getObjectValue();
		}
		return cat;
	}

	public Category findById(final Integer id) {

		Category cat = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Element element = cache.get(id);

		if (null == element) {
			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("select * from categories where id = ?");
				p.setInt(1, id);

				rs = p.executeQuery();

				while (rs.next()) {

					Integer dbId = rs.getInt(1);

					if (dbId.equals(id)) {
						cat = new Category(dbId, rs.getString(2));
						cache.put(new Element(cat.getId(), cat));
						cache.put(new Element(cat.getName(), cat));
						break;
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			cat = (Category) element.getObjectValue();
		}

		return cat;
	}
}
